﻿using Dapper;
using System;
using System.Collections.Generic;
using System.Data;
using System.Data.Common;
using System.Data.SqlClient;
using System.Text;
using System.Text.RegularExpressions;
namespace PageAdmin.Utils.ORM
{
    internal class DbSqlServer : IDbInstance
    {
        private string _Connstr;
        private bool _IsTransaction = false;
        private SqlConnection _Conn = null; //一个实例只声明一次
        private SqlTransaction _SqlTransaction;
        private int? _TimeOut = null;

        /// <summary>
        /// 对象初始化
        /// </summary>
        /// <param name="providerName"></param>
        /// <param name="connectionString"></param>
        public DbSqlServer(string providerName, string connectionString,int? timeOut=null)
        {
            _Connstr = connectionString;//sql server不需要指定Provider，否则会报错
            _Conn = new SqlConnection(_Connstr);
            this._TimeOut = timeOut;
        }

        public DataBaseInfo CurrentDataBaseInfo
        {
            get
            {
                DataBaseInfo dataBaseInfo = new DataBaseInfo();
                dataBaseInfo.DbServer = Regex.Match(_Connstr, @"Data Source=([^;]+)", RegexOptions.IgnoreCase).Groups[1].Value;
                dataBaseInfo.DbName = Regex.Match(_Connstr, @"Initial Catalog=([^;]+)", RegexOptions.IgnoreCase).Groups[1].Value;
                dataBaseInfo.DbUser = Regex.Match(_Connstr, @"User ID=([^;]+)", RegexOptions.IgnoreCase).Groups[1].Value;
                dataBaseInfo.DbPassword = Regex.Match(_Connstr, @"Password=([^;]+)", RegexOptions.IgnoreCase).Groups[1].Value;
                return dataBaseInfo;
            }
        }

        /// <summary>
        /// 获取事务
        /// </summary>
        public IDbTransaction DbTransaction
        {
            get
            {
                return _SqlTransaction;
            }
        }

        public DbConnection Conn
        {
            get
            {
                return _Conn;
            }
        }

        //开启事务，必须在执行任何sql语句前声明
        public IDbTransaction TransactionBegin()
        {
            if (!_IsTransaction)
            {
                if (_Conn.State == ConnectionState.Closed)
                {
                    _Conn.Open();
                }
                _SqlTransaction = _Conn.BeginTransaction();//必须开启才能使用
                _IsTransaction = true;
                return _SqlTransaction;
            }
            else
            {
                return _SqlTransaction;
            }
        }


        //提交事务
        public void TransactionCommit()
        {
            if (_IsTransaction)
            {
                _SqlTransaction.Commit(); //提交事务，Commit()后_SqlTransaction为null;
                _IsTransaction = false;
            }
            Dispose();
        }

        /// <summary>
        /// 事务回滚
        /// </summary>
        public void TransactionRollback()
        {
            if (_IsTransaction)
            {
                _SqlTransaction.Rollback(); //Rollback()后_SqlTransaction为null;
                _IsTransaction = false;
            }
            Dispose();
        }


        /// <summary>
        /// 出错，事务提交后，释放所有资源
        /// </summary>
        public void Dispose()
        {
            if (_Conn != null)
            {
                if (_Conn.State != ConnectionState.Closed)
                {
                    _Conn.Close();
                }
            }
        }

        /// <summary>
        /// 运行sql
        /// </summary>
        /// <param name="sql"></param>
        /// <param name="param"></param>
        /// <returns></returns>
        public int Execute(string sql, DynamicParameters dynamicParameters)
        {
            return Conn.Execute(sql, dynamicParameters, DbTransaction, this._TimeOut);
        }

        /// <summary>
        /// 获取IDataReader
        /// </summary>
        /// <param name="sql"></param>
        /// <param name="param"></param>
        /// <returns></returns>
        public IDataReader GetReader(string sql, DynamicParameters dynamicParameters)
        {
            return Conn.ExecuteReader(sql, dynamicParameters, DbTransaction, this._TimeOut);
        }

        /// <summary>
        /// 获取DataTable
        /// </summary>
        /// <param name="sql"></param>
        /// <param name="param"></param>
        /// <returns></returns>
        public DataTable GetDataTable(string sql, DynamicParameters dynamicParameters)
        {
            DataTable table = new DataTable();
            var reader = GetReader(sql, dynamicParameters);
            table.Load(reader);
            return table;
        }

        /// <summary>
        /// 获取第一行第一列的字段值
        /// </summary>
        /// <param name="sql"></param>
        /// <param name="param"></param>
        /// <returns></returns>
        public object GetField(string sql, DynamicParameters dynamicParameters)
        {
            object val=Conn.ExecuteScalar(sql, dynamicParameters, DbTransaction, this._TimeOut);
            if (val == DBNull.Value)
            {
                val = null;
            }
           return val;
        }

        /// <summary>
        /// 获取数据库结构
        /// </summary>
        /// <param name="table"></param>
        /// <returns></returns>
        public DataTable GetSchemaTable(string table)
        {
            string sql = "select top 1 *  from [" + table + "]";
            SqlCommand sqlCommand = _Conn.CreateCommand();
            sqlCommand.Transaction = _SqlTransaction;
            sqlCommand.CommandText = sql;
            SqlDataAdapter da = new SqlDataAdapter(sqlCommand);
            DataTable dt = new DataTable();
            da.FillSchema(dt, SchemaType.Source);
            return dt;
        }

        /// <summary>
        /// 获取数据库中所有的表
        /// </summary>
        /// <returns></returns>
        public List<string> GetTables()
        {
            try
            {
                List<string> list = new List<string>();
                string sql = "SELECT Name FROM SysObjects Where XType = 'U'";
                IDataReader read = GetReader(sql, null);
                while (read.Read())
                {
                    list.Add(read["Name"].ToString());
                }
                read.Close();
                return list;
            }
            catch (Exception ex)
            {
                Dispose();
                throw new Exception("error：" + ex.Message);
            }
        }

        /// <summary>
        /// 判断表是否存在数据库中
        /// </summary>
        /// <returns></returns>
        public bool TableIsExists(string table)
        {
            string sql = "select id from sysobjects where id = object_id(N'[" + table + "]') and OBJECTPROPERTY(id, N'IsUserTable') = 1";
            object val = GetField(sql, null);
            if (val == null)
            {
                return false;
            }
            else
            {
                return true;
            }
        }

        /// <summary>
        /// 判断字段是否存在数据表中
        /// </summary>
        /// <param name="table"></param>
        /// <param name="columeName"></param>
        /// <returns></returns>
        public bool ColumnIsExists(string table, string columeName)
        {
            string sql = "select id from syscolumns where id = object_id('" + table + "') and name = '" + columeName + "'";
            object val = GetField(sql, null);
            if (val == null)
            {
                return false;
            }
            else
            {
                return true;
            }
        }

        /// <summary>
        /// 创建表
        /// </summary>
        /// <param name="name"></param>
        /// <param name="tableColumnInfoList"></param>
        public void CreateTable(string name, IEnumerable<TableColumnInfo> tableColumnInfoList)
        {
            StringBuilder sb = new StringBuilder();
            sb.Append("create table [dbo].[" + name + "]([id] [int] primary key ID(1,1) NOT NULL");
            TableColumnInfoTransform dataColumnInfoTransform = new TableColumnInfoTransform();
            foreach (var dataColumnInfo in tableColumnInfoList)
            {
                sb.Append("," + dataColumnInfoTransform.Transform(dataColumnInfo));
            }
            sb.Append(")");
            Execute(sb.ToString(), null);
        }

        /// <summary>
        /// 创建字段
        /// </summary>
        /// <param name="tableName"></param>
        /// <param name="tableColumnInfo"></param>
        public void CreateColumn(string tableName, TableColumnInfo tableColumnInfo)
        {
            TableColumnInfoTransform tableColumnInfoTransform = new TableColumnInfoTransform();
            string sql = "alter table [" + tableName + "] add " + tableColumnInfoTransform.Transform(tableColumnInfo);
            Execute(sql, null);
        }

        /// <summary>
        /// 删除表
        /// </summary>
        /// <param name="name"></param>
        public void DropTable(string name)
        {
            string sql = "drop table [" + name + "]";
            Execute(sql, null);
        }

        /// <summary>
        /// 删除字段
        /// </summary>
        /// <param name="tableName"></param>
        /// <param name="name"></param>
        public void DropColumn(string tableName, string name)
        {
            RemoveColumnDefaultValue(tableName, name);
            string sql = "alter table [" + tableName + "] drop column [" + name + "]";
            Execute(sql, null);
        }

        /// <summary>
        /// 表改名
        /// </summary>
        /// <param name="name"></param>
        /// <param name="targetName"></param>
        public void RenameTable(string name, string targetName)
        {
            if (!name.Equals(targetName))
            {
                string sql = "exec sp_rename '" + name + "','" + targetName + "'";
                Execute(sql, null);
            }
        }
        /// <summary>
        /// 列改名
        /// </summary>
        /// <param name="tableName"></param>
        /// <param name="name"></param>
        /// <param name="targetName"></param>
        public void RenameColumn(string tableName, string name, string targetName)
        {
            if (!name.Equals(targetName))
            {
                string sql = "EXEC sp_rename '[" + tableName + "]." + name + "','" + targetName + "','COLUMN'";
                Execute(sql, null);
            }

        }

        /// <summary>
        /// 更改字段
        /// </summary>
        /// <param name="tableName"></param>
        /// <param name="tableColumnInfo"></param>
        public void AlterColumn(string tableName, TableColumnInfo tableColumnInfo)
        {
            TableColumnInfoTransform tableColumnInfoTransform = new TableColumnInfoTransform();
            tableColumnInfoTransform.IncludeDefaultTransform = false;
            RemoveColumnDefaultValue(tableName, tableColumnInfo.Name);
            string sql = "alter table [" + tableName + "] alter column " + tableColumnInfoTransform.Transform(tableColumnInfo);
            Execute(sql, null);
            AlterColumnDefaultValue(tableName, tableColumnInfo);
        }

        /// <summary>
        /// 更改字段默认值
        /// </summary>
        /// <param name="tableName"></param>
        /// <param name="tableColumnInfo"></param>
        public void AlterColumnDefaultValue(string tableName, TableColumnInfo tableColumnInfo)
        {
            TableColumnInfoTransform tableColumnInfoTransform = new TableColumnInfoTransform();
            string defaultSet = tableColumnInfoTransform.FormatDefault(tableColumnInfo);
            if (!string.IsNullOrEmpty(defaultSet))
            {
                string sql = "alter table [" + tableName + "] add CONSTRAINT[DF_" + tableName + "_" + tableColumnInfo.Name + "] " + defaultSet + " FOR [" + tableColumnInfo.Name + "]";
                Execute(sql, null);
            }
        }

        /// <summary>
        /// 删除默认值约束
        /// </summary>
        /// <param name="table"></param>
        /// <param name="field"></param>
        public void RemoveColumnDefaultValue(string table, string field)
        {
            var name = GetField("select b.name from syscolumns a, sysobjects b where a.id = object_id('" + table + "') and b.id = a.cdefault  and a.name = '" + field + "'  and b.name like  'DF%'", null);
            if (name!=null)
            {
                string sql = $"alter  table  [{table}] drop  constraint [{name}]";
                Execute(sql, null);
            }
        }

    }
}
